from Stat import State
from math import sqrt
import MySQLdb
import datetime
import string
import warnings

PREFIXES_TABLE_CREATION_STRING = "CREATE TABLE IF NOT EXISTS prefixes (\
 id INT UNSIGNED AUTO_INCREMENT,\
 ip INT UNSIGNED NOT NULL,\
 mask SMALLINT UNSIGNED NOT NULL,\
 end_ip INT UNSIGNED NOT NULL,\
 start_date DATETIME NOT NULL,\
 end_date DATETIME NOT NULL,\
 conv_date DATETIME,\
 converged ENUM('N', 'Y') NOT NULL,\
 PRIMARY KEY(id),\
 INDEX(ip, mask),\
 INDEX(end_ip))"
               # self.cursor.execute("SELECT id FROM prefixes WHERE ip='%s' AND mask='%s'" % (ip, mask))
UPDATE_TABLE_CREATION_STRING = "CREATE TABLE IF NOT EXISTS updates (prefix_id INT NOT NULL,\
 updates INT NOT NULL,\
 tot_ann INT NOT NULL,\
 dup_ann INT NOT NULL,\
 non_dup_ann INT NOT NULL,\
 tot_with INT NOT NULL,\
 PRIMARY KEY(prefix_id))"

STAT_TABLE_CREATION_STRING = 'CREATE TABLE IF NOT EXISTS %s (prefix_id INT NOT NULL,\
    mean FLOAT NOT NULL,\
    max INT NOT NULL,\
    min INT NOT NULL,\
    std_dev FLOAT NOT NULL,\
    final INT NOT NULL,\
    PRIMARY KEY(prefix_id))'

#this isn't currently used
SWITCH_DIST_TABLE_CREATION_STRING = 'CREATE TABLE IF NOT EXISTS switch_dist (prefix_id INT NOT NULL,\
    mean FLOAT NOT NULL,\
    median FLOAT NOT NULL,\
    max INT NOT NULL,\
    min INT NOT NULL,\
    std_dev FLOAT NOT NULL,\
    PRIMARY KEY(prefix_id))'

NODE_SWITCHES_TABLE_CREATION_STRING = 'CREATE TABLE IF NOT EXISTS node_switches (prefix_id INT NOT NULL,\
    total INT NOT NULL,\
    mean FLOAT NOT NULL,\
    median FLOAT NOT NULL,\
    median_as SMALLINT NOT NULL,\
    max MEDIUMINT NOT NULL,\
    max_as INT NOT NULL,\
    min MEDIUMINT NOT NULL,\
    min_as SMALLINT NOT NULL,\
    std_dev FLOAT NOT NULL,\
    PRIMARY KEY(prefix_id))'

PEER_SWITCHES_TABLE_CREATION_STRING = 'CREATE TABLE IF NOT EXISTS peer_switches (prefix_id INT NOT NULL,\
    total INT NOT NULL,\
    mean FLOAT NOT NULL,\
    median FLOAT NOT NULL,\
    median_as SMALLINT NOT NULL,\
    max MEDIUMINT NOT NULL,\
    max_as SMALLINT NOT NULL,\
    min MEDIUMINT NOT NULL,\
    min_as SMALLINT NOT NULL,\
    std_dev FLOAT NOT NULL,\
    PRIMARY KEY(prefix_id))'

NODE_STATES_TABLE_CREATION_STRING = "CREATE TABLE IF NOT EXISTS node_states (prefix_id INT NOT NULL,\
    as_num SMALLINT NOT NULL,\
    switch_count MEDIUMINT NOT NULL,\
    per_act FLOAT NOT NULL,\
    tot_act INT NOT NULL,\
    act_count SMALLINT NOT NULL,\
    PRIMARY KEY(prefix_id, as_num))"
    
NODE_RANK_TABLE_CREATION_STRING = "CREATE TABLE IF NOT EXISTS node_ranks (prefix_id INT NOT NULL,\
    as_num SMALLINT NOT NULL,\
    rank FLOAT NOT NULL,\
    PRIMARY KEY(prefix_id, as_num))"

PEER_STATES_TABLE_CREATION_STRING = "CREATE TABLE IF NOT EXISTS peer_states (prefix_id INT NOT NULL,\
    as_num SMALLINT NOT NULL,\
    switch_count MEDIUMINT NOT NULL,\
    path VARCHAR(120),\
    per_act FLOAT NOT NULL,\
    tot_act INT NOT NULL,\
    act_count SMALLINT NOT NULL,\
    PRIMARY KEY(prefix_id, as_num))"

NODE_CHILD_STATES_TABLE_CREATION_STRING = "CREATE TABLE IF NOT EXISTS child_states (prefix_id INT NOT NULL,\
    as_num SMALLINT NOT NULL,\
    as_child SMALLINT NOT NULL,\
    switch_count MEDIUMINT NOT NULL,\
    per_act FLOAT NOT NULL,\
    tot_act INT NOT NULL,\
    act_count SMALLINT NOT NULL,\
    PRIMARY KEY(prefix_id, as_num, as_child))"
    
NODE_PARENT_STATES_TABLE_CREATION_STRING = "CREATE TABLE IF NOT EXISTS parent_states (prefix_id INT NOT NULL,\
    as_num SMALLINT,\
    as_parent SMALLINT,\
    switch_count MEDIUMINT,\
    per_act FLOAT,\
    tot_act INT,\
    act_count SMALLINT,\
    PRIMARY KEY(prefix_id, as_num, as_parent))"
    
GRAPH_SIZE_TABLE_CREATION_STRING = 'CREATE TABLE IF NOT EXISTS graph_size (prefix_id INT NOT NULL,\
 graph_width FLOAT,\
 graph_height FLOAT,\
 PRIMARY KEY(prefix_id))'
    
AS_LOOKUP_TABLE_CREATION_STRING = "CREATE TABLE IF NOT EXISTS as_lookup (as_num INT UNIQUE NOT NULL,\
 primary_title VARCHAR(60) NOT NULL DEFAULT 'UNKNOWN',\
 secondary_title VARCHAR(200) NOT NULL DEFAULT 'UNKNOWN',\
 PRIMARY KEY(as_num))"
    
DELETION_TRIGGER_CREATION_STRING = "CREATE TRIGGER prefix_delete BEFORE DELETE ON prefixes \
FOR EACH ROW BEGIN \
    DELETE FROM updates WHERE prefix_id=OLD.id;\
    DELETE FROM node_switches WHERE prefix_id=OLD.id;\
    DELETE FROM peer_switches WHERE prefix_id=OLD.id;\
    DELETE FROM node_states WHERE prefix_id=OLD.id;\
    DELETE from peer_states WHERE prefix_id=OLD.id;\
    DELETE from child_states WHERE prefix_id=OLD.id;\
    DELETE from parent_states WHERE prefix_id=OLD.id;\
    DELETE from paths WHERE prefix_id=OLD.id;\
    DELETE from act_nodes WHERE prefix_id=OLD.id;\
    DELETE from tot_nodes WHERE prefix_id=OLD.id;\
    DELETE from edges WHERE prefix_id=OLD.id;\
    DELETE from graph_size WHERE prefix_id=OLD.id;\
    DELETE from node_ranks WHERE prefix_id=OLD.id;\
END;"
    
MULTIPLE_NODES_CODE = -1
DBCON = ("localhost", "user", "password", "database name")
warnings.formatwarning = lambda x,y,z,w: ""

class StateDBHelper:
    def __init__(self, statTables=[], ip=None, mask=None):
    	self.connection = MySQLdb.connect(DBCON[0], DBCON[1], DBCON[2], DBCON[3])
    	self.cursor = self.connection.cursor()
        self.ip = ip
        self.mask = mask
        self.id = None

        self.cursor.execute(PREFIXES_TABLE_CREATION_STRING)
        self.cursor.execute(UPDATE_TABLE_CREATION_STRING)
        self.cursor.execute(NODE_SWITCHES_TABLE_CREATION_STRING)
        self.cursor.execute(PEER_SWITCHES_TABLE_CREATION_STRING)
        self.cursor.execute(NODE_STATES_TABLE_CREATION_STRING)
        self.cursor.execute(PEER_STATES_TABLE_CREATION_STRING)
        self.cursor.execute(NODE_PARENT_STATES_TABLE_CREATION_STRING)
        self.cursor.execute(NODE_CHILD_STATES_TABLE_CREATION_STRING)        
        self.cursor.execute(GRAPH_SIZE_TABLE_CREATION_STRING)
        self.cursor.execute(AS_LOOKUP_TABLE_CREATION_STRING)
        self.cursor.execute(NODE_RANK_TABLE_CREATION_STRING)
        try:
            self.cursor.execute(DELETION_TRIGGER_CREATION_STRING)
        except:
            None
            #print "Problem creating deletion trigger"
        for stat in statTables:
            self.cursor.execute(STAT_TABLE_CREATION_STRING % stat)
        self.connection.commit()
            
    def setPrefix(self, ip, mask):
        self.ip = ip
        self.mask = mask
        self.id = self.getIdForIpMask(ip, mask)

    def addPrefix(self, startDate, endDate, convTime, converged, updates, totAnn, dupAnn, nonDupAnn, totWith):
        if convTime == 0:
            convDate = "NULL"
        else:
            convDate = "FROM_UNIXTIME(%s)" % convTime
        
        if self.id:
            self.cursor.execute("UPDATE prefixes SET start_date='%s', end_date='%s', conv_date=%s, converged='%s' WHERE id=%s" %\
                (startDate, endDate, convDate, converged, self.id))
        else:
            endIp = self.ip + 2 ** (32 - self.mask) - 1
            self.cursor.execute("INSERT INTO prefixes VALUES(NULL, %s, %s, %s, '%s', '%s', %s, '%s')" %\
                (self.ip,
                 self.mask,
                 endIp,
                 startDate,
                 endDate,
                 convDate,
                 converged))
            self.id = self.connection.insert_id()
            assert(self.id)
           
        self.cursor.execute("REPLACE INTO updates VALUES(%s, %s, %s, %s, %s, %s)" %
            (self.id,
             updates,
             totAnn,
             dupAnn,
             nonDupAnn,
             totWith))

    #median is currently passed in as -1 for values it isn't calculated for
    #total is currently passed in as 0 for values it isn't calculated for
    def addStat(self, stat, mean, max, min, std_dev, final):
        assert(self.id)
    	self.cursor.execute("REPLACE INTO %s VALUES (%s, %s, %s, %s, %s, %s)" % 
            (stat,
             self.id,
             mean,
             max,
             min,
             std_dev,
             final))

    def addPSwitchTimes(self, switchTimes):
        assert(self.id)
    	switchTimes.sort()
    	size = len(switchTimes)
    	max_val = switchTimes[-1]
    	min_val = switchTimes[0]
    	mean = float(sum(switchTimes)) / size
        
    	if size % 2 == 0:
    	    median_val = float(switchTimes[size // 2] + switchTimes[size // 2 - 1])/2
    	else:
    	    median_val = switchTimes[size // 2]
    
        squares = [(value-mean)**2 for value in switchTimes]
        std_dev = sqrt(1.0/size * sum(squares))
    
    	self.cursor.execute("REPLACE INTO switch_dist VALUES(%s, %s, %s, %s, %s, %s)" %
    	    (self.id,
    	    mean,
    	    median_val,
    	    max_val,
    	    min_val,
    	    std_dev))

    def addNodeStates(self, stateDict, utcTime, timeSinceConv):
    	self.insertSwitches(stateDict, "node_switches")
    	self.insertNodeStates(stateDict, utcTime, timeSinceConv)

    def addPathStates(self, stateDict, pathDict, utcTime, timeSinceConv):
    	self.insertSwitches(stateDict, "peer_switches")
    	self.insertPeerStates(stateDict, pathDict, utcTime, timeSinceConv)
        
    def addEdgeStates(self, stateDict, type, utcTime, timeSinceConv):
        assert(self.id)
        for nodeTo, state in stateDict.items():
            nodeToSplit = nodeTo.split('_', 1)
            node = nodeToSplit[0]
            to = nodeToSplit[1]
            totAct = state.activeTime(utcTime)
            perAct = float(totAct)/timeSinceConv
            self.cursor.execute("REPLACE INTO %s_states VALUES (%s, %s, %s, %s, %s, %s, '%s')" %
            (type,
             self.id,
             node,
             to,
             state.switchCount,
             perAct,
             totAct,
             state.activationCount))
            
    def addNodeRanks(self, nodeRanksDict):
        assert(self.id)
        for asNum, stat in nodeRanksDict.items():
            rank = stat.mean()
            self.cursor.execute("REPLACE INTO node_ranks VALUES(%s, %s, %s)" %
            (self.id,
             asNum,
             rank))
            
    def addAS(self, asNum, primaryTitle, secondaryTitle):
        if primaryTitle:
            primaryTitle = string.replace(primaryTitle, "'", "\\'")
        else:
            primaryTitle = 'Unknown'
        if secondaryTitle:
            secondaryTitle = string.replace(secondaryTitle, "'", "\\'")
        else:
            secondaryTitle = 'Unknown'
        self.cursor.execute("REPLACE INTO as_lookup VALUES (%s, '%s', '%s')" %
                            (asNum, primaryTitle, secondaryTitle))
                                        
    def deletePrefix(self):
        self.cursor.execute("DELETE FROM prefixes WHERE id=%s" % self.id)
        self.id = None
        
    def commit(self):
        self.connection.commit()
        
    def addGraphSize(self, width, height):
        assert(self.id)
        self.cursor.execute("REPLACE INTO graph_size VALUES (%s, %s, %s)" % 
            (self.id,
            width,
            height))
        self.connection.commit()

    def insertSwitches(self, stateDict, table):
        assert(self.id)
    	switchCounts = [state.switchCount for state in stateDict.values()]
    	switchCounts.sort()
    	size = len(switchCounts)

    	total = sum(switchCounts)
    	mean = float(total)/size
    	max_val = max(switchCounts)
    	min_val = min(switchCounts)
    	max_node = median_node = min_node = None
        
        if size % 2 == 0:
            median_val = float(switchCounts[size // 2] + switchCounts[size // 2 - 1])/2
            median_node = MULTIPLE_NODES_CODE
        else:
            median_val = switchCounts[size // 2]


    	for node, state in stateDict.items():
    	    count = state.switchCount
    	    if count == max_val:
                if max_node:
                    max_node = MULTIPLE_NODES_CODE
                else:
                    max_node = node
    	    if count == min_val:
        		if min_node:
        		    min_node = MULTIPLE_NODES_CODE
        		else:
        		    min_node = node
                    
    	    if count == median_val:
                median_node = node
    		
        squares = [(count-mean)**2 for count in switchCounts]
        std_dev = sqrt(1.0/size * sum(squares))
        self.cursor.execute("REPLACE INTO %s VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)" %
            (table,
             self.id,
             total,
             mean,
             median_val,
             median_node,
             max_val,
             max_node,
             min_val,
             min_node,
             std_dev))

    def insertNodeStates(self, stateDict, utcTime, timeSinceConv):
        assert(self.id)
    	for node, state in stateDict.items():
            totAct = state.activeTime(utcTime)
            perAct = float(totAct)/timeSinceConv
    	    self.cursor.execute("REPLACE INTO node_states VALUES (%s, %s, %s, %s, %s, '%s')" %
    	    (self.id,
             node,
             state.switchCount,
             perAct,             
             totAct,
             state.activationCount))

    def insertPeerStates(self, stateDict, pathDict, utcTime, timeSinceConv):
        assert(self.id)
        for node, state in stateDict.items():
            totAct = state.activeTime(utcTime)
            perAct = float(totAct)/timeSinceConv          
            self.cursor.execute("REPLACE INTO peer_states VALUES (%s, %s, %s, '%s', %s, %s, '%s')" %
            (self.id,
             node,
             state.switchCount,
             pathDict[node],
             perAct,         
             totAct,
             state.activationCount))

    def query(self, statement):
        self.cursor.execute(statement)
        return self.cursor.fetchall()
    
    def getIdIpMaskList(self):
        self.cursor.execute("SELECT id, INET_NTOA(ip) as ip, mask FROM prefixes")
        return self.cursor.fetchall()
    
    def getIdForIpMask(self, ip, mask):
        self.cursor.execute("SELECT id FROM prefixes WHERE ip=%s AND mask=%s" % (ip, mask))        
        idRow = self.cursor.fetchall()
        if idRow:
            assert(len(idRow) == 1) #There should only be 1 id for any ip/mask pair
            return idRow[0][0]
        else:
            return None    
    
    def getPrefixList(self):
        self.cursor.execute("SELECT id, ip, mask FROM prefixes")
        return self.cursor.fetchall()
    
    def getStat(self, stat):
        self.cursor.execute("SELECT mean, max, min, std_dev, final FROM %s WHERE prefix_id='%s'" % (stat, self.id))
        return self.cursor.fetchone()
    
    def getChildStates(self):
        self.cursor.execute("SELECT as_num, as_child, switch_count, tot_act, act_count FROM child_states WHERE prefix_id=%s" % self.id)
        return self.cursor.fetchall()

    def getParentStates(self):
        self.cursor.execute("SELECT as_num, as_parent, switch_count, tot_act, act_count FROM parent_states WHERE prefix_id=%s" % self.id)
        return self.cursor.fetchall()    
    
    def getNodeStates(self):
        self.cursor.execute("SELECT as_num, switch_count, tot_act, act_count FROM node_states WHERE prefix_id=%s" % self.id)
        return self.cursor.fetchall()
            
    def getPathStates(self):
        self.cursor.execute("SELECT as_num, switch_count, path, tot_act, act_count FROM peer_states WHERE prefix_id=%s" % self.id)
        return self.cursor.fetchall()
    
    def getElapsedTime(self):
        self.cursor.execute("SELECT start_date, end_date, conv_date, converged from prefixes WHERE id=%s" % self.id) 
        dates = self.cursor.fetchone()
        if dates[3] == 'Y':
            return dates[2]-dates[0]
        else:
            return dates[1]-dates[0]
        
    def getExistingDates(self):
        if not self.id:
            return None
        
        self.cursor.execute("SELECT start_date, end_date, conv_date, converged from prefixes WHERE id=%s" % self.id)
        #MySQLdb returns datetime objects for MySQL datatype DATE
        return self.cursor.fetchone()
    
    def getUpdateCounts(self):
        self.cursor.execute("SELECT tot_ann, dup_ann, tot_with FROM updates WHERE prefix_id=%s" % self.id)
        return self.cursor.fetchone()    
            